package com.hylanda.pb.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort.Order;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.hylanda.util.BeanRefUtil;
import com.hylanda.util.annotation.HyTable;

/** 
 * @author zhangy
 * @E-mail:blackoon88@gmail.com 
 * @qq:846579287
 * @version created at：2018年1月19日 上午10:29:42 
 * note
 */
public abstract class SqlServerBaseDao {
	@Resource(name = "sqlseverJdbcTemplate")
	public JdbcTemplate template;
	public  Logger logger =LoggerFactory.getLogger(getClass());
	
	public <T> Page<T>  findPage(Pageable pageable,Class<T> clazz,Map<String,Object> fieldsFilter) {
		int minRowNumber=(pageable.getPageNumber())*pageable.getPageSize();
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		StringBuffer sbfields=new StringBuffer();
		Field[] fields=clazz.getDeclaredFields();
		for(Field field:fields){
			if(!"serialVersionUID".equals(field.getName())&&!field.getType().getSimpleName().equalsIgnoreCase("byte[]")){
				sbfields.append(field.getName()).append(",");
			}
		}
		String fieldsStr=sbfields.toString();
		if(StringUtils.isEmpty(fieldsStr)){
			throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                    "findPage","entity  field" ));
		}
		fieldsStr=fieldsStr.substring(0, fieldsStr.lastIndexOf(","));
		Iterator<Order> iterator=pageable.getSort().iterator();
		StringBuffer sbsort=new StringBuffer();
		while(iterator.hasNext()){
			Order order=iterator.next();
			sbsort.append(order.getProperty()+" ").append(order.getDirection().name()+",");
		}
		String sbsortStr=sbsort.toString();
		if(StringUtils.isEmpty(sbsortStr)){
			throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                    "findPage","sort" ));
		}
		sbsortStr=sbsortStr.substring(0,sbsortStr.lastIndexOf(","));
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TOP ").append(pageable.getPageSize()).append(" ").append(fieldsStr);
		sql.append(" FROM(SELECT ROW_NUMBER () OVER (ORDER BY ").append(sbsortStr)
		.append(" ) RowNumber ,* FROM ").append(tableName);
		if(fieldsFilter!=null&&fieldsFilter.size()>0){
			sql.append(" where ");
			int i=0;
			for(Map.Entry<String, Object> map:fieldsFilter.entrySet()){
				String key=map.getKey();
				Object v=map.getValue();
				if(!(v instanceof String)){
					sql.append(key).append(" = ").append(v);
				}else{
					sql.append(key).append(" like ").append("'"+(String)v+"%'");
				}
				i++;
				if(i<fieldsFilter.size())
					sql.append(" and");
			}
			
		}
		sql.append(" ) A WHERE A.RowNumber >");
		sql.append(minRowNumber);
		
		List<Map<String,Object>> list=template.queryForList(sql.toString());
		List<T> ll=new ArrayList<>();
		for(Map<String,Object> map:list){
//			Class<?> cc=clazz.getClass();
			T entity=null;
			try {
				entity = (T) clazz.newInstance();
			} catch (InstantiationException | IllegalAccessException e) {
				e.printStackTrace();
			}
			BeanRefUtil.setFieldValue2(entity, map);
//			BeanUtils.copyProperties(map, entity);
			ll.add(entity);
		}
		sql=new StringBuffer();
		sql.append("select count(*) from "+tableName);
		if(fieldsFilter!=null&&fieldsFilter.size()>0){
			sql.append(" where ");
			int i=0;
			for(Map.Entry<String, Object> map:fieldsFilter.entrySet()){
				String key=map.getKey();
				Object v=map.getValue();
				if(v==null)
					continue;
				if(!(v instanceof String)){
					sql.append(key).append(" = ").append(v);
				}else{
					sql.append(key).append(" like ").append("'"+(String)v+"%'");
				}
				i++;
				if(i<fieldsFilter.size())
					sql.append(" and");
			}
			
		}
		int total =queryCount(sql.toString());
		return new PageImpl<T>(ll, pageable, total);
	}
	
	public <T>T findEntity(Class<T> clazz,Long id) {
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		StringBuffer sbsql=new StringBuffer();
		sbsql.append("select ");
		StringBuffer sbfields=new StringBuffer();
		Field[] fields=clazz.getDeclaredFields();
		for(Field field:fields){
			if(!"serialVersionUID".equals(field.getName())&&!field.getType().getSimpleName().equalsIgnoreCase("byte[]")){
				sbfields.append(field.getName()).append(",");
			}
		}
		String fieldsStr=sbfields.toString();
		if(StringUtils.isEmpty(fieldsStr)){
			throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                    "findPage","entity  field" ));
		}
		fieldsStr=fieldsStr.substring(0, fieldsStr.lastIndexOf(","));
		sbsql.append(fieldsStr).append(" from ").append(tableName).append(" where id=?");
//		String sql="select id,tableName,tableCname,createTime,isEnable,sort,sql from sys_table_sql where id=?";
		Map<String,Object> map=template.queryForMap(sbsql.toString(), new Object[]{id});
		T bean=null;
		try {
			bean = clazz.newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		BeanRefUtil.setFieldValue2(bean, map);
		return bean;
	}
	public <T>T findEntity(Class<T> clazz,String id,String delByField) {
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		StringBuffer sbsql=new StringBuffer();
		sbsql.append("select ");
		StringBuffer sbfields=new StringBuffer();
		Field[] fields=clazz.getDeclaredFields();
		for(Field field:fields){
			if(!"serialVersionUID".equals(field.getName())&&!field.getType().getSimpleName().equalsIgnoreCase("byte[]")){
				sbfields.append(field.getName()).append(",");
			}
		}
		String fieldsStr=sbfields.toString();
		if(StringUtils.isEmpty(fieldsStr)){
			throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                    "findPage","entity  field" ));
		}
		fieldsStr=fieldsStr.substring(0, fieldsStr.lastIndexOf(","));
		sbsql.append(fieldsStr).append(" from ").append(tableName).append(" where "+delByField+"=?");
//		String sql="select id,tableName,tableCname,createTime,isEnable,sort,sql from sys_table_sql where id=?";
		Map<String,Object> map=template.queryForMap(sbsql.toString(), new Object[]{id});
		T bean=null;
		try {
			bean = clazz.newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		BeanRefUtil.setFieldValue2(bean, map);
		return bean;
	}
	public Map<String, Object> queryForMap(String sql, Object[] objects) {
		Map<String, Object> map=null;
		try {
			map=this.template.queryForMap(sql, objects);
		} catch (DataAccessException e) {
//			e.printStackTrace();
		}
		return map;
	}
	
	public <T> int update(T entity,String updateByField) {
		Class<? extends Object> clazz=entity.getClass();
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		StringBuffer sbsql=new StringBuffer();
		sbsql.append("update ").append(tableName).append(" set ");
		Map<String,Object> map=BeanRefUtil.getFieldValueMap2(entity);
		int i=0;
		int cc=map.size();
		//使用?替换
		for(Map.Entry<String, Object> m:map.entrySet()){
			String key=m.getKey();
			Object v=m.getValue();
			if("id".equals(key)||v==null){
				cc=cc-1;
				continue;
			}
			if((v instanceof Integer)||(v instanceof Long)||(v instanceof Double)||(v instanceof Float)){
				sbsql.append(key).append(" = ").append(v);
			}else{
				sbsql.append(key).append(" = ").append("'"+v+"'");
			}
			i++;
			if(i<cc)
				sbsql.append(" ,");
		}
		sbsql.append(" where "+updateByField+"= ").append("?");
		int num=template.update(sbsql.toString(), new Object[]{map.get(updateByField)});
		return num;
	}
	
	
	
	
	public <T> int delete(Class<T> clazz,Long id,String delByField) {
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		StringBuffer sql=new StringBuffer();
		sql.append("delete from ").append(tableName).append(" where "+delByField+"=?");
//		String sql="delete from sys_table_sql where id=?";
		return template.update(sql.toString(), new Object[]{id});
	}
	
	public <T> T save(final T entity) {
		Class<? extends Object> clazz=entity.getClass();
		String tableName=null;
		if(clazz.isAnnotationPresent(HyTable.class)){
			HyTable table=clazz.getAnnotation(HyTable.class);
			tableName=table.name();
			if(tableName==null){
				 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
	                     "findPage","entity tableName" ));
			}
		}else{
			 throw new IllegalArgumentException(String.format("Unable to invoke method %1$s , because method expects %2$s parameters but were not given",
                     "findPage","entity tableName" ));
		}
		final String tn=tableName;
		KeyHolder keyHolder = new GeneratedKeyHolder(); 
		template.update(new PreparedStatementCreator(){  
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{  
               
                StringBuffer sbsql=new StringBuffer();
                sbsql.append("insert into ").append(tn).append(" (");
                
                Map<String,Object> map=BeanRefUtil.getFieldValueMap2(entity);
        		int cc=0;
        		List<Object> values=new ArrayList<Object>();
        		//使用?替换
        		for(Map.Entry<String, Object> m:map.entrySet()){
        			String key=m.getKey();
        			Object v=m.getValue();
        			if("id".equals(key)||v==null){
        				continue;
        			}
        			values.add(v);
        			sbsql.append(key);
        			cc++;
        			if(cc<map.size())
        				sbsql.append(" ,");
        		}
        		sbsql.append(" ) values(");
        		for(int m=0;m<values.size();m++){
        			sbsql.append("?");
        			if(m<(values.size()-1)){
        				sbsql.append(" ,");
        			}
        		}
        		sbsql.append(")");
        		System.out.println(sbsql);
        		PreparedStatement ps = conn.prepareStatement(sbsql.toString(), Statement.RETURN_GENERATED_KEYS);
        		for(int m=0;m<values.size();m++){
        			ps.setObject(m+1, values.get(m));
        		}
                return ps;
            }  
        },keyHolder);
		 long pid= keyHolder.getKey().longValue();
		 BeanRefUtil.setFieldValue3(entity, pid);
		 return entity;
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @return List<Map<String, Object>>
	 * @throws DataAccessException
	 */
	public List<Map<String, Object>> queryForList(String sql) throws DataAccessException{
		return this.template.queryForList(sql);
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @param args
	 * @return List<Map<String, Object>>
	 * @throws DataAccessException
	 */
	public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException{
		return this.template.queryForList(sql, args);
	}
	
	/**
	 * 查询单列
	 * @param sql
	 * @param claz
	 * @return
	 */
	public <T> List<T> queryForSingleClum(String sql, Class<T> claz) throws DataAccessException{
		RowMapper<T> rowMapper = new SingleColumnRowMapper<T>();
		return this.template.query(sql, rowMapper);
	}
	
	/**
	 * 查询单列
	 * @param sql
	 * @param claz
	 * @return
	 */
	public <T> List<T> queryForSingleClum(String sql, Class<T> claz, Object... args) throws DataAccessException{
		RowMapper<T> rowMapper = new SingleColumnRowMapper<T>();
		return this.template.query(sql, rowMapper, args);
	}
	/**
	 * 查询单列单值
	 * @param sql
	 * @param claz
	 * @param args
	 * @return
	 */
	public <T> T queryforSingleClumSValue(String sql,Class<T> claz,Object... args){
		RowMapper<T> rowMapper = new SingleColumnRowMapper<T>();
		if(!sql.contains("limit 0,1"))
			sql=sql+" limit 0,1";
		return this.template.queryForObject(sql, rowMapper,args);
	}
	/**
	 * 执行查询
	 * @param sql
	 * @param claz
	 * @param args
	 * @return
	 */
	public <T> List<T> query(String sql, Class<T> claz, Object... args) throws DataAccessException{
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(claz);
		return this.template.query(sql, rowMapper, args);
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @param claz
	 * @param args
	 * @return
	 */
	public <T> List<T> query(String sql, Class<T> claz) throws DataAccessException{
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(claz);
		return this.template.query(sql, rowMapper);
	}
	
	/**
	 * 执行Count查询
	 * @param countSql
	 * @return
	 */
	public int queryCount(String countSql) throws DataAccessException{
		Integer number = (Integer)queryForObject(countSql);
		 int count =(number != null ? number.intValue() : 0);
		 return count;
	}
	
	/**
	 * 查询特定类型
	 * @param sql
	 * @param claz
	 * @return
	 */
	public <T> T queryForObject(String sql, Class<T> claz) throws DataAccessException{
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(claz);
		return template.queryForObject(sql, rowMapper);
	}
	public <T> T queryForObject(String sql) throws DataAccessException{
		RowMapper<T> rowMapper = new SingleColumnRowMapper<T>();
		return template.queryForObject(sql, rowMapper);
	}
	/**
	 * 执行新增修改及删除
	 * @param sql
	 * @return
	 */
	public int executeUpdate(String sql) throws DataAccessException{
		return template.update(sql);
		
	};
	
	/**
	 * 执行新增修改
	 * @param sql
	 * @param param
	 * @return
	 */
	public int executeUpdate(String sql, Object... param) throws DataAccessException{
		return template.update(sql, param);
	};
	
}
